USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetProductListForAdministration]    Script Date: 01/12/2014 18:55:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetProductListForAdministration]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetProductListForAdministration]
GO

USE [NGroup_VTL_DB]
GO

/****** Object:  StoredProcedure [dbo].[GetProductListForAdministration]    Script Date: 01/12/2014 18:55:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Bui Thanh Nam
-- Create date: 20131228
-- Description:	Get product list in admin page
-- =============================================
CREATE PROCEDURE [dbo].[GetProductListForAdministration]
	@PageIndex				INT = 1,
	@PageSize				INT = 12,	
	@Author					NVARCHAR(50) = NULL,
	@ProductTypeID			INT = NULL,
	@ProductSupTypeID		INT = NULL,
	@TotalRows				INT = 0 OUTPUT
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE 
			@BeginOffset	INT,
			@EndOffset		INT			

	DECLARE @TAB_PRODUCT_ORDERED TABLE 
	(
		[RowIndex]			INT
		,[ID]				INT
		,[UniqueString]		NVARCHAR(500)		  
		,[Title]			NVARCHAR(500)		
		,[CreatedAt]		DATETIME
		,[CreatedBy]		NVARCHAR(50)
		,[TypeID]			INT
		,[SupTypeID]		INT
	)
	
    ;WITH TAB_PRODUCT_ORDERED AS
	(
		SELECT 
			   ROW_NUMBER() OVER (ORDER BY [dbo].[Products].[CreatedAt] DESC) AS [RowIndex],
			   [dbo].[Products].[ID]			  
			  ,[dbo].[Products].[UniqueString]			  
			  ,[dbo].[Products].[Title]
			  ,[dbo].[Products].[CreatedAt]
			  ,[dbo].[Products].[CreatedBy]
			  ,[dbo].[Products].[TypeID]
			  ,[dbo].[Products].[SupTypeID]		  	  
		FROM [dbo].[Products] WITH (NOLOCK) INNER JOIN [dbo].[GenericTypes] WITH (NOLOCK)
			ON [dbo].[Products].[TypeID] = [dbo].[GenericTypes].[ID]
		WHERE 
			([dbo].[Products].[CreatedBy] = @Author OR @Author IS NULL)
			AND ([dbo].[Products].[TypeID] = @ProductTypeID OR @ProductTypeID IS NULL)
			AND ([dbo].[Products].[SupTypeID] = @ProductSupTypeID OR @ProductSupTypeID IS NULL)
			AND [dbo].[Products].[IsActive] <> 0
			AND [dbo].[GenericTypes].[IsActive] <> 0
	)
	INSERT INTO @TAB_PRODUCT_ORDERED
	SELECT *
	FROM TAB_PRODUCT_ORDERED
	
	SELECT @TotalRows = MAX([RowIndex])
	FROM @TAB_PRODUCT_ORDERED

	SELECT @BeginOffset = (@PageSize * (@PageIndex - 1)) + 1, 
		   @EndOffset = (@BeginOffset - 1) + @PageSize;

	SELECT * 
	FROM @TAB_PRODUCT_ORDERED
	WHERE [RowIndex] BETWEEN @BeginOffset AND @EndOffset	

END







GO

